package com.youhp.spire.xls.demo.Charts.ManipulateChart;

import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;
import java.awt.*;

public class DiscontinuousData {

    public static void main(String[] args)
    {
        Workbook book = new Workbook();
        //Load file from disk
        book.loadFromFile("data/DiscontinuousData.xlsx");

        //Get the first sheet
        Worksheet sheet = book.getWorksheets().get(0);

        //Add a chart
        Chart chart = sheet.getCharts().add(ExcelChartType.ColumnClustered);
        chart.setSeriesDataFromRange(false);

        //Set the position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(10);
        chart.setRightColumn(10);
        chart.setBottomRow(24);

        //Add a series
        ChartSerie cs1 = (ChartSerie)chart.getSeries().add();

        //Set the name of the cs1
        cs1.setName(sheet.getCellRange("B1").getValue());

        //Set discontinuous values for cs1
        cs1.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
                .addCombinedRange(sheet.getCellRange("A8:A9")));
        cs1.setValues(sheet.getCellRange("B2:B3").addCombinedRange(sheet.getCellRange("B5:B6"))
                .addCombinedRange(sheet.getCellRange("B8:B9")));

        //Set the chart type
        cs1.setSerieType(ExcelChartType.ColumnClustered);

        //Add a series
        ChartSerie cs2 = (ChartSerie)chart.getSeries().add();
        cs2.setName(sheet.getCellRange("C1").getValue());
        cs2.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
                .addCombinedRange(sheet.getCellRange("A8:A9")));
        cs2.setValues(sheet.getCellRange("C2:C3").addCombinedRange(sheet.getCellRange("C5:C6"))
                .addCombinedRange(sheet.getCellRange("C8:C9")));
        cs2.setSerieType(ExcelChartType.ColumnClustered);

        chart.setChartTitle("Chart");
        chart.getChartTitleArea().getFont().setSize(20);
        chart.getChartTitleArea().setColor(Color.black);

        chart.getPrimaryValueAxis().hasMajorGridLines(false);

        //Save the result file
        book.saveToFile("output/DiscontinuousDataResult.xlsx", ExcelVersion.Version2010);

    }
}
